#pragma once

#include <spdlog/spdlog.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include "../util/config.hh"
#include "../util/dbconn.hh"
#include "../poco/user.hh"

namespace WordMemory
{
    class UserRepository
    {
    public:
        static UserRepository& instance()
        {
            static UserRepository inst;
            return inst;
        }

    public:
        User authorize(const std::string& username, const std::string& password)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "SELECT id, username, password, real_name, created_at FROM users WHERE username = ? AND password = ?"));

                pstmt->setString(1, username);
                pstmt->setString(2, password);
                std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

                if (res->next())
                {
                    return User(
                        res->getInt("id"),
                        res->getString("username"),
                        res->getString("password"),
                        res->getString("real_name"),
                        res->getString("created_at")
                    );
                }
                else
                {
                    throw RepositoryExcept("User not found");
                }
            }
            catch (const sql::SQLException& e)
            {
                throw RepositoryExcept("User not found, " + std::string(e.what()));
            }
        }

        User createUser(const std::string& username, const std::string& password, const std::string& real_name)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "INSERT INTO users (username, password, real_name) VALUES (?, ?, ?)"));
                pstmt->setString(1, username);
                pstmt->setString(2, password);
                pstmt->setString(3, real_name);
                pstmt->executeUpdate();

                std::unique_ptr<sql::PreparedStatement> id_stmt(
                    conn.get()->prepareStatement("SELECT LAST_INSERT_ID() AS id"));
                std::unique_ptr<sql::ResultSet> id_res(id_stmt->executeQuery());

                int new_id = 0;
                if (id_res->next())
                    new_id = id_res->getInt("id");
                else
                    throw RepositoryExcept("Failed to retrieve last insert ID");

                std::unique_ptr<sql::PreparedStatement> select_stmt(
                    conn.get()->prepareStatement(
                        "SELECT id, username, password, real_name, created_at FROM users WHERE id = ?"));
                select_stmt->setInt(1, new_id);
                std::unique_ptr<sql::ResultSet> res(select_stmt->executeQuery());

                if (res->next())
                {
                    return User(
                        res->getInt("id"),
                        res->getString("username"),
                        res->getString("password"),
                        res->getString("real_name"),
                        res->getString("created_at")
                    );
                }
                else
                {
                    throw RepositoryExcept("Failed to retrieve newly created user");
                }
            }
            catch (const sql::SQLException& e)
            {
                throw RepositoryExcept("Failed to create user: " + std::string(e.what()));
            }
        }

        User getUser(int user_id)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "SELECT id, username, password, real_name, created_at FROM users WHERE id = ?"));
                pstmt->setInt(1, user_id);
                std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

                if (res->next())
                {
                    return User(
                        res->getInt("id"),
                        res->getString("username"),
                        res->getString("password"),
                        res->getString("real_name"),
                        res->getString("created_at")
                    );
                }
                else
                {
                    throw RepositoryExcept("User not found");
                }
            }
            catch (const sql::SQLException& e)
            {
                throw RepositoryExcept("Failed to get user: " + std::string(e.what()));
            }
        }

        User updateUser(int user_id, const std::string& real_name)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "UPDATE users SET real_name = ? WHERE id = ?"));
                pstmt->setString(1, real_name);
                pstmt->setInt(2, user_id);
                pstmt->executeUpdate();

                std::unique_ptr<sql::PreparedStatement> select_stmt(
                    conn.get()->prepareStatement(
                        "SELECT id, username, password, real_name, created_at FROM users WHERE id = ?"));
                select_stmt->setInt(1, user_id);
                std::unique_ptr<sql::ResultSet> res(select_stmt->executeQuery());

                if (res->next())
                {
                    return User(
                        res->getInt("id"),
                        res->getString("username"),
                        res->getString("password"),
                        res->getString("real_name"),
                        res->getString("created_at")
                    );
                }
                else
                {
                    throw RepositoryExcept("User not found or no changes made");
                }
            }
            catch (const sql::SQLException& e)
            {
                throw RepositoryExcept("Failed to update user: " + std::string(e.what()));
            }
        }

    private:
        UserRepository() = default;
        UserRepository(const UserRepository&) = delete;
        UserRepository& operator=(const UserRepository&) = delete;
    };
}